package com.ai.bitcoin.jpa.dao;

import com.ai.bitcoin.jpa.entity.BccPriceInfoDetail;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.Date;
import java.util.List;

public interface BccPriceInfoDetailDao extends JpaRepository<BccPriceInfoDetail, String>, JpaSpecificationExecutor<BccPriceInfoDetail> {

    /**
     * 查询价格 分时与K线
     *
     * @param bccId
     * @param dateBegin 开始时间
     * @param minutes   K分组 分时图为固定为1
     * @return 分时列表
     */
    @Query(nativeQuery = true, value = "" +
            "SELECT " +
            "uuid() as id" +
            ", :bccId as bcc_id" +
            ", 0 as price_usd" +
            ", 0 as price_btc" +
            ", :dateBegin as create_time" +
            ", convert_tz(from_unixtime(round(unix_timestamp(price_time) / (:minutes * 60)) * :minutes * 60), @@session.time_zone, '+00:00') as price_time" +
            ", round(avg(price_cny), 4) price_cny " +
            ", round(avg(ma5_cny), 4) ma5_cny " +
            ", round(avg(ma10_cny), 4) ma10_cny " +
            ", round(avg(ma20_cny), 4) ma20_cny " +
            ", round(avg(ma30_cny), 4) ma30_cny " +
            "FROM bcc_price_info_detail detail " +
            "WHERE detail.bcc_id = :bccId " +
            "  AND detail.price_time >= :dateBegin " +
            "GROUP BY round(unix_timestamp(detail.price_time) / (:minutes * 60)) " +
            "ORDER BY detail.price_time ASC" +
            "")
    List<BccPriceInfoDetail> queryLine(@Param("bccId") String bccId, @Param("dateBegin") Date dateBegin, @Param("minutes") long minutes);
}
